package com.lifeverse.repository;

import com.lifeverse.entity.AuditLog;
import com.lifeverse.entity.enums.AuditAction;
import com.lifeverse.entity.enums.AuditResult;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 审计日志数据访问层
 */
@Repository
public interface AuditLogRepository extends JpaRepository<AuditLog, Long> {
    
    /**
     * 根据用户ID查找审计日志
     */
    Page<AuditLog> findByUserId(String userId, Pageable pageable);
    
    /**
     * 根据用户名查找审计日志
     */
    Page<AuditLog> findByUsername(String username, Pageable pageable);
    
    /**
     * 根据操作类型查找审计日志
     */
    Page<AuditLog> findByAction(AuditAction action, Pageable pageable);
    
    /**
     * 根据操作结果查找审计日志
     */
    Page<AuditLog> findByResult(AuditResult result, Pageable pageable);
    
    /**
     * 根据资源类型和资源ID查找审计日志
     */
    Page<AuditLog> findByResourceTypeAndResourceId(String resourceType, String resourceId, Pageable pageable);
    
    /**
     * 根据时间范围查找审计日志
     */
    Page<AuditLog> findByTimestampBetween(LocalDateTime startTime, LocalDateTime endTime, Pageable pageable);
    
    /**
     * 根据IP地址查找审计日志
     */
    Page<AuditLog> findByIpAddress(String ipAddress, Pageable pageable);
    
    /**
     * 查找敏感操作的审计日志
     */
    Page<AuditLog> findByIsSensitiveTrue(Pageable pageable);
    
    /**
     * 根据风险等级查找审计日志
     */
    Page<AuditLog> findByRiskLevelGreaterThanEqual(Integer riskLevel, Pageable pageable);
    
    /**
     * 查找失败的操作
     */
    @Query("SELECT a FROM AuditLog a WHERE a.result IN ('FAILURE', 'DENIED', 'TIMEOUT') ORDER BY a.timestamp DESC")
    Page<AuditLog> findFailedOperations(Pageable pageable);
    
    /**
     * 查找特定时间范围内的失败操作
     */
    @Query("SELECT a FROM AuditLog a WHERE a.result IN ('FAILURE', 'DENIED', 'TIMEOUT') " +
           "AND a.timestamp BETWEEN :startTime AND :endTime ORDER BY a.timestamp DESC")
    List<AuditLog> findFailedOperationsBetween(@Param("startTime") LocalDateTime startTime, 
                                              @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计特定用户的操作次数
     */
    @Query("SELECT COUNT(a) FROM AuditLog a WHERE a.userId = :userId AND a.timestamp BETWEEN :startTime AND :endTime")
    Long countUserOperations(@Param("userId") String userId, 
                           @Param("startTime") LocalDateTime startTime, 
                           @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计特定操作类型的次数
     */
    @Query("SELECT COUNT(a) FROM AuditLog a WHERE a.action = :action AND a.timestamp BETWEEN :startTime AND :endTime")
    Long countActionOperations(@Param("action") AuditAction action,
                             @Param("startTime") LocalDateTime startTime,
                             @Param("endTime") LocalDateTime endTime);
    
    /**
     * 查找异常IP地址的操作（同一IP短时间内大量操作）
     */
    @Query("SELECT a.ipAddress, COUNT(a) as operationCount FROM AuditLog a " +
           "WHERE a.timestamp BETWEEN :startTime AND :endTime " +
           "GROUP BY a.ipAddress HAVING COUNT(a) > :threshold " +
           "ORDER BY operationCount DESC")
    List<Object[]> findSuspiciousIpAddresses(@Param("startTime") LocalDateTime startTime,
                                           @Param("endTime") LocalDateTime endTime,
                                           @Param("threshold") Long threshold);
    
    /**
     * 查找用户的登录历史
     */
    @Query("SELECT a FROM AuditLog a WHERE a.userId = :userId AND a.action IN ('LOGIN', 'LOGOUT', 'LOGIN_FAILED') " +
           "ORDER BY a.timestamp DESC")
    Page<AuditLog> findUserLoginHistory(@Param("userId") String userId, Pageable pageable);
    
    /**
     * 查找系统操作（无用户ID的操作）
     */
    @Query("SELECT a FROM AuditLog a WHERE a.userId IS NULL ORDER BY a.timestamp DESC")
    Page<AuditLog> findSystemOperations(Pageable pageable);
    
    /**
     * 根据多个条件查找审计日志
     */
    @Query("SELECT a FROM AuditLog a WHERE " +
           "(:userId IS NULL OR a.userId = :userId) AND " +
           "(:action IS NULL OR a.action = :action) AND " +
           "(:result IS NULL OR a.result = :result) AND " +
           "(:resourceType IS NULL OR a.resourceType = :resourceType) AND " +
           "(:startTime IS NULL OR a.timestamp >= :startTime) AND " +
           "(:endTime IS NULL OR a.timestamp <= :endTime) AND " +
           "(:minRiskLevel IS NULL OR a.riskLevel >= :minRiskLevel) " +
           "ORDER BY a.timestamp DESC")
    Page<AuditLog> findByMultipleConditions(@Param("userId") String userId,
                                          @Param("action") AuditAction action,
                                          @Param("result") AuditResult result,
                                          @Param("resourceType") String resourceType,
                                          @Param("startTime") LocalDateTime startTime,
                                          @Param("endTime") LocalDateTime endTime,
                                          @Param("minRiskLevel") Integer minRiskLevel,
                                          Pageable pageable);
    
    /**
     * 删除指定时间之前的审计日志（用于数据清理）
     */
    @Query("DELETE FROM AuditLog a WHERE a.timestamp < :cutoffTime")
    void deleteOldAuditLogs(@Param("cutoffTime") LocalDateTime cutoffTime);
    
    /**
     * 统计各种操作类型的数量
     */
    @Query("SELECT a.action, COUNT(a) FROM AuditLog a " +
           "WHERE a.timestamp BETWEEN :startTime AND :endTime " +
           "GROUP BY a.action ORDER BY COUNT(a) DESC")
    List<Object[]> getActionStatistics(@Param("startTime") LocalDateTime startTime,
                                     @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计各种结果类型的数量
     */
    @Query("SELECT a.result, COUNT(a) FROM AuditLog a " +
           "WHERE a.timestamp BETWEEN :startTime AND :endTime " +
           "GROUP BY a.result ORDER BY COUNT(a) DESC")
    List<Object[]> getResultStatistics(@Param("startTime") LocalDateTime startTime,
                                     @Param("endTime") LocalDateTime endTime);
}